package demo.office;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.jnlp.PersistenceService;

import com.alibaba.fastjson.JSON;

import com.hissy.common.office.ExcelUtil;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Created by zhangyanfei on 2017/12/7.
 * 读写 excel
 *
 * @author zhangyanfei
 * @date 2017/12/07
 */
public class ExcelDemo {
    public static void main(String[] args) throws Exception {
        FileInputStream fileInputStream = new FileInputStream("数据修复.xlsx");
        List<Map<Integer, String>> result = ExcelUtil.getImportExcelData(fileInputStream, "数据修复.xlsx", 0, 0, 200);
        List<RmaRefundCompleteMsg> rmaRefundCompleteMsgs = new ArrayList<>();
        // SELECT a.number,a.order_number from (SELECT COUNT(order_number) number,order_number from yitiao_newrma
        // where type=11 and create_time>'2018-9-14 00:00:00' GROUP BY order_number) a where a.number>1;
        for (Map map : result) {
            RmaRefundCompleteMsg rmaRefundCompleteMsg = new RmaRefundCompleteMsg();
            rmaRefundCompleteMsg.setCompleteTime(
                DateUtils.parseDate(String.valueOf(map.get(0)), "yyyy-MM-dd HH:mm:ss"));
            rmaRefundCompleteMsg.setCreateTime(DateUtils.parseDate(String.valueOf(map.get(1)), "yyyy-MM-dd HH:mm:ss"));
            rmaRefundCompleteMsg.setCreator((String)map.get(2));
            rmaRefundCompleteMsg.setCreatorId(Integer.valueOf((String)map.get(3)));
            rmaRefundCompleteMsg.setOperator((String)map.get(4));
            rmaRefundCompleteMsg.setOperatorId(Integer.valueOf((String)map.get(5)));
            rmaRefundCompleteMsg.setOrderItemId(Integer.valueOf((String)map.get(6)));
            rmaRefundCompleteMsg.setOrderNumber((String)map.get(7));
            rmaRefundCompleteMsg.setQuantity(Integer.valueOf((String)map.get(8)));
            rmaRefundCompleteMsg.setRefundedPointAmount(Integer.valueOf((String)map.get(9)));
            rmaRefundCompleteMsg.setRefundedThirdPartyPayAmount(PriceUtil.convertPenny(Double.valueOf(
                (String)map.get(10))));
            rmaRefundCompleteMsg.setReturnBizId((String)map.get(11));
            rmaRefundCompleteMsg.setReturnRmaBizType((String)map.get(12));
            rmaRefundCompleteMsg.setRmaNumber((String)map.get(13));
            rmaRefundCompleteMsg.setRmaType(RmaType.valueOf((String)map.get(14)));
            rmaRefundCompleteMsg.setSkuId(Integer.valueOf((String)map.get(15)));
            rmaRefundCompleteMsg.setSubOrderNo((String)map.get(16));
            rmaRefundCompleteMsg.setTotalRefundedAmount(PriceUtil.convertPenny(Double.valueOf(
                (String)map.get(17))));
            rmaRefundCompleteMsg.setUserId(Integer.valueOf((String)map.get(18)));
            if (map.get(19) != null) {
                rmaRefundCompleteMsg.setWarehouseId(Integer.valueOf((String)map.get(19)));
            }
            rmaRefundCompleteMsgs.add(rmaRefundCompleteMsg);
        }

        System.out.println(JSON.toJSONString(rmaRefundCompleteMsgs));
    }

    private static void createExcel() throws IOException {
        String header = "姓名,年龄,地址,电话";
        Workbook workbook = ExcelUtil.exportExcelFile2007(header);
        Workbook workbook1 = ExcelUtil.exportExcelFile2003(header);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet sheet1 = workbook1.getSheetAt(0);
        List<Person> personList = new ArrayList<>();
        Person temp = new Person();
        temp.setUserName("zhangyanfei");
        temp.setAge(10);
        temp.setAddress("sync");
        temp.setMobile("13027188938");
        personList.add(temp);
        personList.add(temp);
        personList.add(temp);
        personList.add(temp);
        personList.add(temp);
        personList.add(temp);
        int i = 1;
        for (Person person : personList) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(person.getUserName());
            row.createCell(1).setCellValue(person.getAge());
            row.createCell(2).setCellValue(person.getAddress());
            row.createCell(3).setCellValue(person.getMobile());

            Row row1 = sheet1.createRow(i);
            row1.createCell(0).setCellValue(person.getUserName());
            row1.createCell(1).setCellValue(person.getAge());
            row1.createCell(2).setCellValue(person.getAddress());
            row1.createCell(3).setCellValue(person.getMobile());
            i++;
        }
        FileOutputStream outputStream = new FileOutputStream("test.xlsx");
        workbook.write(outputStream);
        FileOutputStream outputStream1 = new FileOutputStream("test.xls");
        workbook1.write(outputStream1);
    }

    /**
     *
     */
    public static void explainExcel() throws IOException {
        FileInputStream fileInputStream = new FileInputStream("数据修复.xlsx");
        List<Map<Integer, String>> result = ExcelUtil.getImportExcelData(fileInputStream, "test.xlsx", 0, 1, 200);
    }
}
